﻿using Dapper;
using Inventorycount.API.Read.Application.Command;
using Inventorycount.Domain;
using Inventorycount.Dto;
using Inventorycount.ErrorCode;
using MediatR;
using MySqlConnector;
using System.Data;
using System.Text;

namespace Inventorycount.API.Read.Application.CommandHandLer
{
    public class GetTransfeCommandHandLer : IRequestHandler<GetTransfeCommand, APIPaing<TransferDto>>
    {
        private readonly IDbConnection repository;

        public GetTransfeCommandHandLer(IDbConnection repository)
        {
           this.repository = repository;
        }


        public async Task<APIPaing<TransferDto>> Handle(GetTransfeCommand request, CancellationToken cancellationToken)
        {
            APIPaing<TransferDto> result = new APIPaing<TransferDto>();
            result.Code = ResultCode.Ok;
            result.Message = "查询成功";
            try
            {
                // 1. 构建主查询SQL (MySQL语法)
                var sql = new StringBuilder(@"
                    SELECT 
                    p.Id,
                    p.Transfer_no,
                    p.Transfer_type,
                    p.Create_time,
                    p.Out_warehouse_id,
                    p.In_warehouse_id,
                    p.Operator_id,
                    p.Status,
                    p.Remark,
                    u.Name,
                    d.Id,
                    d.Transfer_id,
                    d.Goods_id AS Goods_id,
                    d.Goods_name AS Goods_name,
                    d.goods_code AS goods_code,
                    d.goods_type AS goods_type,
                    d.Specification,
                    d.Unit,
                    d.outbound_status AS outbound_status,
                    d.inbound_status AS inbound_status,
                    d.Out_stock AS Out_stock,
                    d.In_stock AS In_stock,
                    d.Quantity,
                    d.Price,
                    d.Amount,
                    d.Remark
                FROM Wms_Transfer_order p
                JOIN Wms_Transfer_detail d ON p.Id = d.Transfer_id
                JOIN Wms_User u ON p.Operator_id = u.Id
                WHERE p.IsDeleted = 0");

                var parameters = new DynamicParameters();

                // 2. 修正查询条件
                if (!string.IsNullOrWhiteSpace(request.Transfer_no))
                {
                    sql.Append(" AND p.Transfer_no LIKE @Transfer_no");
                    parameters.Add("Transfer_no", $"%{request.Transfer_no}%");
                }

                if (!string.IsNullOrWhiteSpace(request.Status))
                {
                    sql.Append(" AND p.Status = @Status");
                    parameters.Add("Status", request.Status);
                }

                if (request.Outbound_status.HasValue)
                {
                    sql.Append(" AND d.Outbound_status = @Outbound_status");
                    parameters.Add("Outbound_status", request.Outbound_status.Value);
                }

                if (request.Inbound_status.HasValue)
                {
                    sql.Append(" AND d.Inbound_status = @Inbound_status");
                    parameters.Add("Inbound_status", request.Inbound_status.Value);
                }

                // 3. 修正分页查询
                sql.Append(" ORDER BY p.Id DESC");
                sql.Append(" LIMIT @Offset, @PageSize");
                parameters.Add("Offset", (request.PageIndex - 1) * request.PageSize);
                parameters.Add("PageSize", request.PageSize);

                // 4. 修正COUNT查询
                var countSql = new StringBuilder(@"
            SELECT COUNT(DISTINCT p.Id) 
            FROM Wms_Transfer_order p
            JOIN Wms_Transfer_detail d ON p.Id = d.Transfer_id
            JOIN Wms_User u ON p.Operator_id = u.Id
            WHERE p.IsDeleted = 0");

                if (!string.IsNullOrWhiteSpace(request.Transfer_no))
                {
                    countSql.Append(" AND p.Transfer_no LIKE @Transfer_no");
                }
                if (!string.IsNullOrWhiteSpace(request.Status))
                {
                    countSql.Append(" AND p.Status = @Status");
                }
                if (request.Outbound_status.HasValue)
                {
                    countSql.Append(" AND d.Outbound_status = @Outbound_status");
                }
                if (request.Inbound_status.HasValue)
                {
                    countSql.Append(" AND d.Inbound_status = @Inbound_status");
                }

                // 执行查询
                var count = await repository.ExecuteScalarAsync<int>(countSql.ToString(), parameters);

                // 使用Dapper的多映射功能
                var materialDictionary = new Dictionary<int, TransferDto>();

                await repository.QueryAsync<TransferDto,Transfer_detailDto, TransferDto>(
                    sql.ToString(),
                    (material, info) =>
                    {
                        if (!materialDictionary.TryGetValue(material.Id, out var materialEntry))
                        {
                            materialEntry = material;
                            materialEntry.Detail = new List<Transfer_detailDto>();
                            materialDictionary.Add(material.Id, materialEntry);
                        }

                        if (info != null)
                        {
                            materialEntry.Detail.Add(info);
                        }
                        return materialEntry;
                    },
                    parameters,
                    splitOn: "Id");

                // 返回分页结果
                result.Data = materialDictionary.Values.ToList();
                result.TotalCount = count;
                result.TotalPage = (int)Math.Ceiling(count * 1.0 / request.PageSize);
                result.Message = "查询成功";
            }
            catch (Exception ex)
            {
                result.Code = ResultCode.Fail;
                result.Message = $"查询失败: {ex.Message}";
            }
            

            return result;
        }
    }
}